# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function: 数据初始化对比：cvs1.0与cvs2.0 LP列表对比（LP名称、类型、资本来源、管理基金）
注：管理基金 去掉了
"""

def searchCvs1LpList():
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM dc_lp a WHERE a.`status`='1' ORDER BY a.id DESC"
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50

    for index in range(pageCount):
        limitStart = index * 50

        print("limitStart====================================================",limitStart)

        sql = "SELECT a.id AS lpId, a.name AS LpName,a.lp_type, a.capital_from_type AS source, null AS fund_short_name,a.ext1 AS beizhu " \
              "FROM dc_lp a " \
              "WHERE a.`status`=1 " \
              "ORDER BY a.name DESC limit %d,50 "%limitStart

        # sql = "SELECT a.id AS lpId, a.name AS LpName,a.lp_type, a.capital_from_type AS source, null AS fund_short_name,a.ext1 AS beizhu " \
        #       "FROM datacenter_online_20180609.dc_lp a " \
        #       "WHERE a.`status`=1 " \
        #       "ORDER BY a.name DESC limit 13997,13999 "

        # sql = "SELECT a.id AS lpId, a.name AS LpName,a.lp_type, a.capital_from_type AS source, null AS fund_short_name,a.ext1 AS beizhu " \
        #       "FROM datacenter_online_20180609.dc_lp a " \
        #       "WHERE a.`status`=1 AND a.name='陆家嘴财富管理（上海）有限公司'"

        cursor.execute(sql)
        lpResults1 = cursor.fetchall()
        fields = cursor.description
        compareCvs2LPList(lpResults1,fields)

    db.close()

def compareCvs2LPList(lpResults1,fields):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cvs_prod", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    lpNameStr = ''
    for index in range(len(lpResults1)):
        print(index)
        lpName1 = lpResults1[index].get(u"LpName")
        if lpName1 != None:
            lpName1 = MySQLdb.escape_string(lpName1)
            lpNameStr += ",'"+lpName1+"'"
    print(lpNameStr)
    lpNameStr=lpNameStr[1:]

    viewLpsql = "SELECT full_name AS lpId,full_name AS LpName, lp_type, source, fund_short_name, enable_search AS beizhu " \
                "FROM view_lp WHERE full_name IN (%s) ORDER BY full_name DESC"%lpNameStr
    cursor.execute(viewLpsql)
    viewLpResults2 = cursor.fetchall()
    for index in range(len(lpResults1)):
        lpName1 = lpResults1[index].get(u"LpName")
        lpType1 = lpResults1[index].get(u"lp_type")
        source1 = lpResults1[index].get(u"source")
        lpId1 = lpResults1[index].get(u"lpId")

        for viewLpIndex in range(len(viewLpResults2)):
            lpName2= viewLpResults2[viewLpIndex].get(u"LpName")
            if lpName2 == lpName1:
                lpType2 = viewLpResults2[viewLpIndex].get(u"lp_type")
                source2 = viewLpResults2[viewLpIndex].get(u"source")
                flag = (lpName1 == lpName2 and lpType1 == lpType2 and source1 == source2)
                # if flag == True:
                #     fund_short_name2 = viewLpResults2[viewLpIndex].get(u"fund_short_name")
                #     #对比1.0和2.0库中lp的管理基金
                #     cpmpareCvs1and2LpManageCompany(lpResults1[index],fields,lpId1,fund_short_name2)
                if flag!=True:
                     lpResults1[index].update(beizhu=u'cvs1和2中viewLp 名称\类型\资本来源 不完全匹配，此为cvs1中数据')
                     writeExcel(lpResults1[index],fields)
                     print("LpName2==="+lpName2)
                     viewLpResults2[viewLpIndex].update(beizhu=u'cvs1和2中viewLp 名称\类型\资本来源 不完全匹配，此为cvs2中数据')
                     writeExcel(viewLpResults2[viewLpIndex],fields)
                     break
                else:
                    break
            else:
                continue

    db.close()

def cpmpareCvs1and2LpManageCompany(lpResults1,fields,lpId1,fund_short_name2):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT b.cn_name FROM dc_fund_manage_company a, dc_fund b " \
          "WHERE a.context_type=2 AND a.fund_id = b.id AND a.`status`=1 " \
          "AND a.context_id='%s'"%lpId1

    cursor.execute(sql)
    lpManageCompanyResults1 = cursor.fetchall()
    for index in range(len(lpManageCompanyResults1)):
        ManageCompanyCnName1 = lpManageCompanyResults1[index].get(u"cn_name")
        flag = (ManageCompanyCnName1 !=None and fund_short_name2 == None)
        if flag:
            lpResults1.update(beizhu=u'cvs2中此lp管理基金缺失此条管理基金，此为cvs1中数据：'+ManageCompanyCnName1)
            writeExcel(lpResults1,fields)
        elif ManageCompanyCnName1 in fund_short_name2:
            continue
        else:
            lpResults1.update(beizhu=u'cvs2中此lp管理基金缺失此条管理基金，此为cvs1中数据：'+ManageCompanyCnName1)
            writeExcel(lpResults1,fields)

    db.close()

def writeExcel(results,fields):

    fileName = 'dataViewLp.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
